from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from gps_config import (logging, GpsConfig)


class gps_data(object):
    # 打开数据库
    engine = create_engine(GpsConfig.MYSQL_URL, echo=False, pool_pre_ping=True)
    DBSession = sessionmaker(bind=engine)
    db_session = DBSession()

    def getdata(self, dtu, qrydate):
        trackList = []
        workdatas = self.db_session.execute(
            "select lon,lat,speed,time from device_obd_gps_info_{0} where dtu='{1}'"
            .format(qrydate, dtu))
        for data in workdatas:
            item = {}
            if data["lon"] != '' and data["lat"] != '':
                item["lon"] = data["lon"]
                item["lat"] = data["lat"]
                item["speed"] = data["speed"]
                item["time"] = data["time"].strftime('%Y-%m-%d %H:%M:%S')
                trackList.append(item)
        datas = {}
        datas["data"] = {}
        datas["data"]["trackList"] = trackList
        return datas

    def getidlist(self, qrydate):
        idlist = []
        datas = self.db_session.execute(
            "select distinct dtu from device_obd_gps_info_{0}".format(qrydate))
        for data in datas:
            idlist.append(data["dtu"])
        return idlist

    def writedata(self, work_date, dtu, area, block, d1, d2):
        self.db_session.execute(
            'insert into device_obd_work_daily_record_info(dtu,area,block,mileage,workmileage,work_date,update_time) values ("{0}","{1}","{2}","{3}","{4}","{5}",CURRENT_TIMESTAMP) on duplicate key update area="{1}",block="{2}",mileage="{3}",workmileage="{4}",update_time=CURRENT_TIMESTAMP'
            .format(dtu, area, block, d1, d2, work_date))
        

    def commit(self):
        self.db_session.commit()